package com.yunshipei.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;

import com.yunshipei.EnterApplication;
import com.yunshipei.model.DateTime;
import com.yunshipei.model.Departments;
import com.yunshipei.model.NotifyInfo;
import com.yunshipei.model.SpeechRecognition;
import com.yunshipei.model.UserInfo;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import io.rong.app.model.GroupCreateInfo;
import io.rong.imlib.NativeObject;
import io.rong.imlib.model.Group;
import io.rong.imlib.model.Message;
import io.rong.message.TextMessage;

public class DatabaseUtil {
    private NotifiDbHelper notifiDbHelper = null;
    private Context context = null;
    private EnterplorerManager manager;

    public DatabaseUtil() {
        this.context = EnterApplication.getApp();
        manager = EnterplorerManager.getInstance(context);
        notifiDbHelper = new NotifiDbHelper(context);
    }

    /**
     * 插入数据
     *
     * @param userInfo
     */
    public synchronized void Insert(UserInfo userInfo) {
        SQLiteDatabase db = manager.getWritableDatabase();
        String sql = "insert into " + EnterplorerHelper.TABLE_USUAL_USER_LIST
                + "(uuid,name,password,department,departmentId,companyId,position,tel,email,createdTime,avatar,isAdmin) values ("
                + "'" + userInfo.getUuid() + "' ," + "'" + userInfo.getName() + "','" + userInfo.getPassword() + "','" +
                userInfo.getDepartment() + "','" + userInfo.getDepartmentId() + "','" + userInfo.getCompanyId() + "','" + userInfo.getPosition() + "','" + userInfo.getTel() + "','"
                + userInfo.getEmail() + "','" + userInfo.getCreatedTime() + "','" + userInfo.getAvatar() + "','" + userInfo.getIsAdmin() + "')";
        try {
            if (!db.isReadOnly())
                db.execSQL(sql);
            else {
                Log.e("插入用户信息", "数据库是只读");
            }
        } catch (SQLException e) {
            Log.e("err", "insert failed" + (e != null ? e.getMessage() : ""));
        }
// finally {
//            db.close();
//        }
    }


    /**
     * 插入数据 更新表信息
     *
     * @param userInfos
     */
    public synchronized void InsertAllUser(List<UserInfo> userInfos) {
        String tableName = EnterplorerHelper.TABLE_USUAL_USER_LIST;
        SQLiteDatabase db = manager.getWritableDatabase();
        db.beginTransaction();
        db.execSQL(manager.getEnterplorerHelper().getUserListTableSql());
        int count = db.delete(tableName, null, null);
        Log.d("_delete_user_count", count + "");
        for (int j = 0; j < userInfos.size(); j++) {
            UserInfo userInfo = userInfos.get(j);
            String img = userInfo.getAvatar();
            if (TextUtils.isEmpty(img)) {
                userInfos.get(j).setAvatar("notimage");
            }
            String sql = "insert into " + tableName
                    + "(uuid,name,password,department,departmentId,companyId,position,tel,email,createdTime,avatar,isAdmin) values ("
                    + "'" + userInfo.getUuid() + "' ," + "'" + userInfo.getName() + "','" + userInfo.getPassword() + "','" +
                    userInfo.getDepartment() + "','" + userInfo.getDepartmentId() + "','" + userInfo.getCompanyId() + "','" + userInfo.getPosition() + "','" + userInfo.getTel() + "','"
                    + userInfo.getEmail() + "','" + userInfo.getCreatedTime() + "','" + userInfo.getAvatar() + "','" + userInfo.getIsAdmin() + "')";
            try {
                db.execSQL(sql);
            } catch (SQLException e) {
                Log.e("err", "insert failed" + (e != null ? e.getMessage() : ""));
            }
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    /**
     * 插入提醒信息
     * (_id integer primary key autoincrement,focus,tip,speech,content,name,dateTime,notifiStatus )";
     *
     * @param ni
     * @return
     */
    public int insertAlarm(NotifyInfo ni) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        Cursor cur = null;
        String sql = "insert into " + EnterplorerHelper.TABLE_NOTIFI
                + "(focus,tip,speech,content,name,dateTime,notifiStatus,datee,dateOrig,time,timeOrig,repetionTime,delayTime,forwardTime,shake,userId, musicStatus,music,voiceRepore) values ("
                + "'" + ni.getSpeechRecognition().getFocus() + "','" + ni.getSpeechRecognition().getTip() + "','" + ni.getSpeechRecognition().getSpeech() + "','" + ni.getSpeechRecognition().getContent() + "','"
                + ni.getSpeechRecognition().getName() + "','" + ni.getSpeechRecognition().getDateTime().toString() + "','" + ni.getNotifiStatus() + "','" + ni.getSpeechRecognition().getDateTime().getDate() + "','"
                + ni.getSpeechRecognition().getDateTime().getDateOrig() + "','" + ni.getSpeechRecognition().getDateTime().getTime() + "','" + ni.getSpeechRecognition().getDateTime().getTimeOrig() + "','"
                + ni.getRepetitionTime() + "','" + ni.getDelayTime() + "','" + ni.getForwardTime() + "','" + String.valueOf(ni.getShake()) + "','" + ni.getUserId() + "','" + String.valueOf(ni.getMusicStatus()) + "','" + ni.getMusic() + "','" + String.valueOf(ni.getVoiceRepore()) + "')";
        try {
            db.execSQL(sql);
            cur = db.rawQuery("select last_insert_rowid() from " + EnterplorerHelper.TABLE_NOTIFI, null);
            cur.moveToFirst();
            return cur.getInt(0);
        } catch (SQLException e) {
            Log.e("err", "insert failed");
            return 0;
        } finally {
            cur.close();
            db.close();
        }
    }

    /**
     * @param bool == true, 查询还没有完成的提醒
     *             bool == false 查询已经完成的提醒
     * @return
     */
    public List<NotifyInfo> queryNotifiAll(boolean bool) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        List<NotifyInfo> list = new ArrayList<>();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_NOTIFI, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            NotifyInfo ni = new NotifyInfo();
            SpeechRecognition sp = new SpeechRecognition();
            sp.setFocus(cursor.getString(cursor.getColumnIndex("focus")));
            sp.setTip(cursor.getString(cursor.getColumnIndex("tip")));
            sp.setSpeech(cursor.getString(cursor.getColumnIndex("speech")));
            sp.setContent(cursor.getString(cursor.getColumnIndex("content")));
            sp.setName(cursor.getString(cursor.getColumnIndex("name")));
            DateTime dateTime = new DateTime();
            dateTime.setDate(cursor.getString(cursor.getColumnIndex("datee")));
            dateTime.setDateOrig(cursor.getString(cursor.getColumnIndex("dateOrig")));
            dateTime.setTime(cursor.getString(cursor.getColumnIndex("time")));
            dateTime.setTimeOrig(cursor.getString(cursor.getColumnIndex("timeOrig")));
            sp.setDateTime(dateTime);
            ni.setRepetitionTime(cursor.getString(cursor.getColumnIndex("repetionTime")));
            ni.setDelayTime(cursor.getString(cursor.getColumnIndex("delayTime")));
            ni.setForwardTime(cursor.getString(cursor.getColumnIndex("forwardTime")));
            ni.setShake(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("shake"))).booleanValue());
            ni.setMusic(cursor.getString(cursor.getColumnIndex("music")));
            ni.setNotifiStatus(cursor.getString(cursor.getColumnIndex("notifiStatus")));
            ni.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
            ni.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
            ni.setMusicStatus(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("musicStatus"))));
            ni.setSpeechRecognition(sp);
            ni.setVoiceRepore(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("voiceRepore"))));

            if (bool) {
                if (dateTime.getInfactDate().after(new Date())) {
                    list.add(ni);
                } else if (ni.getRepetitionTime().length() > 0) {
                    list.add(ni);
                }
            } else {
                if (dateTime.getInfactDate().before(new Date()) && ni.getRepetitionTime().length() <= 0) {
                    list.add(ni);
                }
            }
        }
        cursor.close();
        db.close();
        return list;
    }


    public List<NotifyInfo> queryNotifiAll(boolean bool, String userId) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        List<NotifyInfo> list = new ArrayList<>();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_NOTIFI, new String[]{"focus", "tip", "speech", "content", "name", "datee", "dateOrig",
                "time", "timeOrig", "repetionTime", "delayTime", "forwardTime", "shake", "music", "notifiStatus", "_id", "userId", "musicStatus",
                "voiceRepore"}, "userId like ?", new String[]{userId}, null, null, null);
        while (cursor.moveToNext()) {
            NotifyInfo ni = new NotifyInfo();
            SpeechRecognition sp = new SpeechRecognition();
            sp.setFocus(cursor.getString(cursor.getColumnIndex("focus")));
            sp.setTip(cursor.getString(cursor.getColumnIndex("tip")));
            sp.setSpeech(cursor.getString(cursor.getColumnIndex("speech")));
            sp.setContent(cursor.getString(cursor.getColumnIndex("content")));
            sp.setName(cursor.getString(cursor.getColumnIndex("name")));
            DateTime dateTime = new DateTime();
            dateTime.setDate(cursor.getString(cursor.getColumnIndex("datee")));
            dateTime.setDateOrig(cursor.getString(cursor.getColumnIndex("dateOrig")));
            dateTime.setTime(cursor.getString(cursor.getColumnIndex("time")));
            dateTime.setTimeOrig(cursor.getString(cursor.getColumnIndex("timeOrig")));
            sp.setDateTime(dateTime);
            ni.setRepetitionTime(cursor.getString(cursor.getColumnIndex("repetionTime")));
            ni.setDelayTime(cursor.getString(cursor.getColumnIndex("delayTime")));
            ni.setForwardTime(cursor.getString(cursor.getColumnIndex("forwardTime")));
            ni.setShake(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("shake"))).booleanValue());
            ni.setMusic(cursor.getString(cursor.getColumnIndex("music")));
            ni.setNotifiStatus(cursor.getString(cursor.getColumnIndex("notifiStatus")));
            ni.set_id(cursor.getInt(cursor.getColumnIndex("_id")));
            ni.setUserId(cursor.getString(cursor.getColumnIndex("userId")));
            ni.setMusicStatus(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("musicStatus"))));
            ni.setSpeechRecognition(sp);
            ni.setVoiceRepore(Boolean.valueOf(cursor.getString(cursor.getColumnIndex("voiceRepore"))));
            if (bool) {
                Log.e("ni.getRepetitionTime", ni.getRepetitionTime());
                if (dateTime.getInfactDate().after(new Date())) {
                    list.add(ni);
                } else if (ni.getRepetitionTime().length() > 0) {
                    list.add(ni);
                }
            } else {
                if (dateTime.getInfactDate().before(new Date()) && ni.getRepetitionTime().length() <= 0) {
                    list.add(ni);
                }
            }
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 更新提醒状态
     *
     * @param _id
     * @param status
     */
    public void updateNotifiStatus(int _id, String status) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("notifiStatus", status);
        String whereStr[] = {String.valueOf(_id)};
        db.update(EnterplorerHelper.TABLE_NOTIFI, cv, "_id = ?", whereStr);
        db.close();
        Log.d("db 操作", "update--notifiStatus");
    }

    /**
     * 更新提醒是否震动
     *
     * @param _id
     * @param shake
     */
    public void updateNotifiShake(int _id, String shake) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("shake", shake);
        String whereStr[] = {String.valueOf(_id)};
        db.update(EnterplorerHelper.TABLE_NOTIFI, cv, "_id = ?", whereStr);
        db.close();
    }

    /**
     * 删除提醒
     *
     * @param _id
     */
    public void deleteNotifi(int _id) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        String whereStr[] = {String.valueOf(_id)};
        db.delete(EnterplorerHelper.TABLE_NOTIFI, "_id = ?", whereStr);
        Log.d("db 操作", "删除提醒");
        db.close();
    }

    public int findMaxValueforId() {
        int id = 0;
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_NOTIFI, new String[]{"_id"}, null, null, null, null, "_id asc");
        while (cursor.moveToNext()) {
            id = cursor.getInt(cursor.getColumnIndex("_id"));
        }
        cursor.close();
        db.close();
        return id;
    }

    /**
     * 更新提醒信息
     *
     * @param ni
     */
    public void updateNotifi(NotifyInfo ni) {
        SQLiteDatabase db = notifiDbHelper.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("focus", ni.getSpeechRecognition().getFocus());
        cv.put("tip", ni.getSpeechRecognition().getTip());
        cv.put("speech", ni.getSpeechRecognition().getSpeech());
        cv.put("content", ni.getSpeechRecognition().getContent());
        cv.put("name", ni.getSpeechRecognition().getName());
        cv.put("dateTime", ni.getSpeechRecognition().getDateTime().toString());
        cv.put("notifiStatus", ni.getNotifiStatus());
        cv.put("datee", ni.getSpeechRecognition().getDateTime().getDate());
        cv.put("dateOrig", ni.getSpeechRecognition().getDateTime().getDateOrig());
        cv.put("time", ni.getSpeechRecognition().getDateTime().getTime());
        cv.put("timeOrig", ni.getSpeechRecognition().getDateTime().getTimeOrig());
        cv.put("repetionTime", ni.getRepetitionTime());
        cv.put("delayTime", ni.getDelayTime());
        cv.put("forwardTime", ni.getForwardTime());
        cv.put("shake", String.valueOf(ni.getShake()));
        cv.put("userId", ni.getUserId());
        cv.put("musicStatus", String.valueOf(ni.getMusicStatus()));
        cv.put("music", ni.getMusic());
        Log.e("music db :", ni.getMusic());
        cv.put("voiceRepore", String.valueOf(ni.getVoiceRepore()));
        String whereStr[] = {String.valueOf(ni.get_id())};
        db.update(EnterplorerHelper.TABLE_NOTIFI, cv, "_id = ?", whereStr);
        db.close();
    }

    /**
     * 查询所有数据
     * <p>
     * //	 *
     */
    public List<UserInfo> queryAll() {
        SQLiteDatabase db = manager.getWritableDatabase();
        List<UserInfo> list = new ArrayList<UserInfo>();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_USUAL_USER_LIST, null, null, null, null, null, null);
        while (cursor != null && cursor.moveToNext()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setUuid(cursor.getString(cursor.getColumnIndex("uuid")));
            userInfo.setName(cursor.getString(cursor.getColumnIndex("name")));
            userInfo.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            userInfo.setDepartment(cursor.getString(cursor.getColumnIndex("department")));
            userInfo.setDepartmentId(cursor.getString(cursor.getColumnIndex("departmentId")));
            userInfo.setCompanyId(cursor.getString(cursor.getColumnIndex("companyId")));
            String postionString = cursor.getString(cursor.getColumnIndex("position"));
            postionString = TextUtils.isEmpty(postionString) || "null".equals(postionString) ? "" : postionString;
            userInfo.setPosition(postionString);
            userInfo.setTel(cursor.getString(cursor.getColumnIndex("tel")));
            userInfo.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            userInfo.setCreatedTime(cursor.getString(cursor.getColumnIndex("createdTime")));
            userInfo.setAvatar(cursor.getString(cursor.getColumnIndex("avatar")));
            userInfo.setIsAdmin(cursor.getString(cursor.getColumnIndex("isAdmin")));
            userInfo.setSelecteState(false);        //默认为非选中状态
            list.add(userInfo);
        }
        cursor.close();
//        db.close();
        return list;
    }

    /**
     * 用户头像更新
     *
     * @param id
     * @return
     */
    public void updateUserInfoById(String id, String avatarUri) {
        SQLiteDatabase db = manager.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("avatar", avatarUri);
        String whereStr[] = {id};
        db.update(EnterplorerHelper.TABLE_USUAL_USER_LIST, cv, "uuid like ?", whereStr);
//        db.close();
    }


    /**
     * 按姓名进行查找并排序
     */
    public List<UserInfo> queryBydepartmentId(String departmentId) {
        SQLiteDatabase db = manager.getWritableDatabase();
        List<UserInfo> list = new ArrayList<UserInfo>();
        //uuid primary key,name,password,department,departmentId,companyId,position,tel,email,createdTime
        Cursor cursor = db.query(EnterplorerHelper.TABLE_USUAL_USER_LIST, new String[]{"uuid", "name", "password", "department", "departmentId", "companyId", "position", "tel"
                , "email", "createdTime"}, "departmentId like ? ", new String[]{"%" + departmentId + "%"}, null, null, "departmentId asc");
        while (cursor.moveToNext()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setUuid(cursor.getString(cursor.getColumnIndex("uuid")));
            userInfo.setName(cursor.getString(cursor.getColumnIndex("name")));
            userInfo.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            userInfo.setDepartment(cursor.getString(cursor.getColumnIndex("department")));
            userInfo.setDepartmentId(cursor.getString(cursor.getColumnIndex("departmentId")));
            userInfo.setCompanyId(cursor.getString(cursor.getColumnIndex("companyId")));
            userInfo.setPosition(cursor.getString(cursor.getColumnIndex("position")));
            userInfo.setTel(cursor.getString(cursor.getColumnIndex("tel")));
            userInfo.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            userInfo.setCreatedTime(cursor.getString(cursor.getColumnIndex("createdTime")));
            list.add(userInfo);
        }
        cursor.close();
//        db.close();
        return list;
    }

    /**
     * @param uuid
     * @return
     */
    public synchronized io.rong.imlib.model.UserInfo queryByUuid(String uuid) {
        SQLiteDatabase db = manager.getWritableDatabase();
        io.rong.imlib.model.UserInfo userInfo = null;
        Cursor cursor = db.query(EnterplorerHelper.TABLE_USUAL_USER_LIST, new String[]{"uuid", "name", "password", "department", "departmentId", "companyId", "position", "tel"
                , "email", "createdTime", "avatar"}, "uuid like ? ", new String[]{"%" + uuid + "%"}, null, null, "departmentId asc");

        while (cursor.moveToNext()) {
            String avatarString = cursor.getString(cursor.getColumnIndex("avatar"));
            if (!"notimage".equals(avatarString)) {
                Uri addrUri = Uri.parse(avatarString);
                userInfo = new io.rong.imlib.model.UserInfo(cursor.getString(cursor.getColumnIndex("uuid")),
                        cursor.getString(cursor.getColumnIndex("name")), addrUri);
            } else {
                userInfo = new io.rong.imlib.model.UserInfo(cursor.getString(cursor.getColumnIndex("uuid")),
                        cursor.getString(cursor.getColumnIndex("name")), null);
            }
        }
        cursor.close();
//        db.close();
        return userInfo;
    }


    /**
     * 按id查询
     */
    public synchronized UserInfo queryUserInfoByid(String id) {

        SQLiteDatabase db = manager.getWritableDatabase();
        UserInfo person = null;
        Cursor cursor = db.query(EnterplorerHelper.TABLE_USUAL_USER_LIST, new String[]{"uuid", "name", "password", "department", "departmentId", "companyId", "position", "tel"
                , "email", "createdTime", "avatar", "isAdmin"}, "uuid like ? ", new String[]{"%" + id + "%"}, null, null, "departmentId asc");
        while (cursor.moveToNext()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setUuid(cursor.getString(cursor.getColumnIndex("uuid")));
            userInfo.setName(cursor.getString(cursor.getColumnIndex("name")));
            userInfo.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            userInfo.setDepartment(cursor.getString(cursor.getColumnIndex("department")));
            userInfo.setDepartmentId(cursor.getString(cursor.getColumnIndex("departmentId")));
            userInfo.setCompanyId(cursor.getString(cursor.getColumnIndex("companyId")));
            userInfo.setPosition(cursor.getString(cursor.getColumnIndex("position")));
            userInfo.setTel(cursor.getString(cursor.getColumnIndex("tel")));
            userInfo.setEmail(cursor.getString(cursor.getColumnIndex("email")));
            userInfo.setCreatedTime(cursor.getString(cursor.getColumnIndex("createdTime")));
            userInfo.setAvatar(cursor.getString(cursor.getColumnIndex("avatar")));
            userInfo.setIsAdmin(cursor.getString(cursor.getColumnIndex("isAdmin")));
            person = userInfo;
            break;
        }
        cursor.close();
//        db.close();
        return person;
    }

    /**
     * 插入部门信息
     * _id,"+"departmentId primary key,departmentName,companyId,companyName,"
     * + "parentDepartmentId,memberNumber,createdTime,updatedTime
     */
    public boolean Insert(Departments departments) {
        SQLiteDatabase db = manager.getWritableDatabase();
        String sql = "insert into " + EnterplorerHelper.TABLE_USUAL_USER_LIST
                + "(_id,departmentId,departmentName,companyId,companyName,parentDepartmentId,memberNumber,createdTime,updatedTime) values ("
                + "'" + departments.get_id() + "' ," + "'" + departments.getDepartmentId() + "','" + departments.getDepartmentName() + "','" +
                departments.getCompanyId() + "','" + departments.getCompanyName() + "','" + departments.getParentDepartmentId() + "','" + departments.getMemberNumber() + "','"
                + departments.getCreatedTime() + "','" + departments.getUpdatedTime() + "'" + ")";
        try {
            db.execSQL(sql);
            return true;
        } catch (SQLException e) {
            Log.e("err", "insert failed");
            return false;
        }
//        finally {
//            db.close();
//        }

    }

    /**
     * 查询部门信息
     * 根据parentdepartmentId来查询部门信息
     * _id,"+"departmentId primary key,departmentName,companyId,companyName,"
     * + "parentDepartmentId,memberNumber,createdTime,updatedTime
     */
    public Departments queryByid(String parentDepartmentId) {

        SQLiteDatabase db = manager.getWritableDatabase();
        Departments departments = new Departments();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_DEPARTMENT, new String[]{"_id", "departmentId", "departmentName", "companyId", "companyName",
                "parentDepartmentId", "memberNumber", "createdTime", "updatedTime"}, "parentDepartmentId=?", new String[]{parentDepartmentId + ""}, null, null, null);
        while (cursor.moveToNext()) {
            departments.set_id(cursor.getString(cursor.getColumnIndex("_id")));
            departments.setDepartmentId(cursor.getString(cursor.getColumnIndex("departmentId")));
            departments.setDepartmentName(cursor.getString(cursor.getColumnIndex("departmentName")));
            departments.setCompanyId(cursor.getString(cursor.getColumnIndex("companyId")));
            departments.setCompanyName(cursor.getString(cursor.getColumnIndex("companyName")));
            departments.setParentDepartmentId(cursor.getString(cursor.getColumnIndex("parentDepartmentId")));
            departments.setMemberNumber(cursor.getString(cursor.getColumnIndex("memberNumber")));
            departments.setCreatedTime(cursor.getString(cursor.getColumnIndex("createdTime")));
            departments.setUpdatedTime(cursor.getString(cursor.getColumnIndex("updatedTime")));

        }
        cursor.close();
//        db.close();
        return departments;
    }


    /**
     * 删除数据表
     */
    public void delateTable(String tableName) {
        SQLiteDatabase db = manager.getWritableDatabase();
        String sql = "drop table if exists " + tableName;
        db.execSQL(sql);
//        db.close();
    }

    /**
     * 创建表
     */
    public void CreateTable() {
        SQLiteDatabase db = manager.getWritableDatabase();
        db.execSQL(manager.getEnterplorerHelper().getUserListTableSql());
//        db.close();
    }

    public void createGroupTable() {
        SQLiteDatabase db = manager.getWritableDatabase();
        db.execSQL(manager.getEnterplorerHelper().groupInfoString);
//        db.close();
    }

    /**
     * "id primary key,name,portraitUri,introduce,"
     * + "number,max_number,create_user_id,create_dateTime
     */
    public synchronized boolean insertGroup(GroupCreateInfo groupCreateInfo) {
        SQLiteDatabase db = manager.getWritableDatabase();
        String sql = "insert into " + EnterplorerHelper.TABLE_GROUP
                + "(id,name,portraitUri,introduce,number,max_number,create_user_id,create_dateTime, groupMembers) values ("
                + "'" + groupCreateInfo.getId() + "','" + groupCreateInfo.getName() + "','" + groupCreateInfo.getPortraitUri() + "','" +
                groupCreateInfo.getIntroduce() + "','" + groupCreateInfo.getNumber() + "','" + groupCreateInfo.getMax_number() + "','" + groupCreateInfo.getCreate_user_id() + "','"
                + groupCreateInfo.getCreate_dateTime() + "','" + groupCreateInfo.getGourpMembers() + "');";
        try {
            db.execSQL(sql);
            return true;
        } catch (SQLException e) {
            Log.e("err", "insert failed");
            return false;
        }
//        finally {
//            db.close();
//        }
    }


    /**
     * "id primary key,name,portraitUri,introduce,"
     * + "number,max_number,create_user_id,create_dateTime
     */
    public synchronized void insertGroups(List<GroupCreateInfo> groupCreateInfos) {
        SQLiteDatabase db = manager.getWritableDatabase();

        db.beginTransaction();
        for (GroupCreateInfo groupCreateInfo : groupCreateInfos) {
            String sql = "insert into " + EnterplorerHelper.TABLE_GROUP
                    + "(id,name,portraitUri,introduce,number,max_number,create_user_id,create_dateTime, groupMembers) values ("
                    + "'" + groupCreateInfo.getId() + "','" + groupCreateInfo.getName() + "','" + groupCreateInfo.getPortraitUri() + "','" +
                    groupCreateInfo.getIntroduce() + "','" + groupCreateInfo.getNumber() + "','" + groupCreateInfo.getMax_number() + "','" + groupCreateInfo.getCreate_user_id() + "','"
                    + groupCreateInfo.getCreate_dateTime() + "','" + groupCreateInfo.getGourpMembers() + "');";
            try {
                db.execSQL(sql);
            } catch (SQLException e) {
                e.printStackTrace();
                Log.e("err", "insert failed");
            }
        }
        db.setTransactionSuccessful();
        db.endTransaction();

    }


    /**
     * 查询所有的im聊天群组信息
     *
     * @return
     */
    public synchronized List<GroupCreateInfo> queryGroups() {
        List<GroupCreateInfo> list = new ArrayList<GroupCreateInfo>();
        SQLiteDatabase db = manager.getWritableDatabase();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_GROUP, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            GroupCreateInfo groups = new GroupCreateInfo();
            groups.setId(cursor.getString(cursor.getColumnIndex("id")));
            groups.setName(cursor.getString(cursor.getColumnIndex("name")));
            groups.setPortraitUri(cursor.getString(cursor.getColumnIndex("portraitUri")));
            groups.setIntroduce(cursor.getString(cursor.getColumnIndex("introduce")));
            groups.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
            groups.setMax_number(cursor.getInt(cursor.getColumnIndex("max_number")));
            groups.setCreate_user_id(cursor.getString(cursor.getColumnIndex("create_user_id")));
            groups.setCreate_dateTime(cursor.getString(cursor.getColumnIndex("create_dateTime")));
            groups.setGourpMembers(cursor.getString(cursor.getColumnIndex("groupMembers")));
            list.add(groups);
        }
        return list;
    }

    public List<Group> queryRongGroup() {
        List<Group> list = new ArrayList<Group>();
        SQLiteDatabase db = manager.getWritableDatabase();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_GROUP, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            Group groups = new Group(cursor.getString(cursor.getColumnIndex("id")),
                    cursor.getString(cursor.getColumnIndex("name")),
                    Uri.parse(cursor.getString(cursor.getColumnIndex("portraitUri"))));
            list.add(groups);
        }
        cursor.close();
//        db.close();
        return list;
    }

    /**
     * 删除群组信息
     *
     * @param id
     */
    public void delateGroup(String id) {
        SQLiteDatabase db = manager.getWritableDatabase();
        int cursor = db.delete(EnterplorerHelper.TABLE_GROUP, "id like ?", new String[]{"%" + id + "%"});
//        db.close();
    }

    public void resetGroupsTable(List<GroupCreateInfo> groupCreateInfos) {
        SQLiteDatabase db = manager.getWritableDatabase();
        db.beginTransaction();
        int count = db.delete(EnterplorerHelper.TABLE_GROUP, null, null);
        Log.d("_delete_count", count + "");
        for (GroupCreateInfo groupCreateInfo : groupCreateInfos) {
            String sql = "insert into " + EnterplorerHelper.TABLE_GROUP
                    + "(id,name,portraitUri,introduce,number,max_number,create_user_id,create_dateTime, groupMembers) values ("
                    + "'" + groupCreateInfo.getId() + "','" + groupCreateInfo.getName() + "','" + groupCreateInfo.getPortraitUri() + "','" +
                    groupCreateInfo.getIntroduce() + "','" + groupCreateInfo.getNumber() + "','" + groupCreateInfo.getMax_number() + "','" + groupCreateInfo.getCreate_user_id() + "','"
                    + groupCreateInfo.getCreate_dateTime() + "','" + groupCreateInfo.getGourpMembers() + "');";
            try {
                db.execSQL(sql);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    /**
     * 更新ImGroup信息
     *
     * @param groupCreateInfo
     */
    public void updateGroup(GroupCreateInfo groupCreateInfo) {
        SQLiteDatabase db = manager.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("id", groupCreateInfo.getId());
        cv.put("name", groupCreateInfo.getName());
        cv.put("portraitUri", groupCreateInfo.getPortraitUri());
        cv.put("introduce", groupCreateInfo.getIntroduce());
        cv.put("number", groupCreateInfo.getNumber());
        cv.put("max_number", groupCreateInfo.getMax_number());
        cv.put("create_user_id", groupCreateInfo.getCreate_user_id());
        cv.put("create_dateTime", groupCreateInfo.getCreate_dateTime());
        cv.put("groupMembers", groupCreateInfo.getGourpMembers());
        String whereStr[] = {groupCreateInfo.getId()};
        db.update(EnterplorerHelper.TABLE_GROUP, cv, "id like ?", whereStr);
//        db.close();
    }

    /**
     * 通过Id查询群组信息
     *
     * @param id
     * @return
     */
    public synchronized GroupCreateInfo findGroupById(String id) {
        GroupCreateInfo groupCreateInfo = null;
        SQLiteDatabase db = manager.getWritableDatabase();
        Cursor cursor = db.query(EnterplorerHelper.TABLE_GROUP, new String[]{"id", "name", "portraitUri", "introduce",
                "number", "max_number", "create_user_id", "create_dateTime", "groupMembers"}, "id like ?", new String[]{"%" + id + "%"}, null, null, null);
//        synchronized (Cursor.class) {
        while (cursor != null && cursor.moveToNext()) {
            GroupCreateInfo groups = new GroupCreateInfo();
            groups.setId(cursor.getString(cursor.getColumnIndex("id")));
            groups.setName(cursor.getString(cursor.getColumnIndex("name")));
            groups.setPortraitUri(cursor.getString(cursor.getColumnIndex("portraitUri")));
            groups.setIntroduce(cursor.getString(cursor.getColumnIndex("introduce")));
            groups.setNumber(cursor.getInt(cursor.getColumnIndex("number")));
            groups.setMax_number(cursor.getInt(cursor.getColumnIndex("max_number")));
            groups.setCreate_user_id(cursor.getString(cursor.getColumnIndex("create_user_id")));
            groups.setCreate_dateTime(cursor.getString(cursor.getColumnIndex("create_dateTime")));
            groups.setGourpMembers(cursor.getString(cursor.getColumnIndex("groupMembers")));
            groupCreateInfo = groups;
        }
        if (cursor != null && !cursor.isClosed()) ;
        cursor.close();
//        }
////        if (db != null && db.isOpen())
//            db.close();
        return groupCreateInfo;
    }

    /**
     * 删除数据库
     *
     * @param file
     */
    public void deleteDataBase(File file) {
        SQLiteDatabase db = manager.getWritableDatabase();
        db.deleteDatabase(file);
//        db.close();
    }

    /**
     * 更新或插入
     *
     * @param groupCreateInfo
     */
    public void updateOrInsertGroups(GroupCreateInfo groupCreateInfo) {
        GroupCreateInfo groupCreateInfo1 = findGroupById(groupCreateInfo.getId());
        if (groupCreateInfo1 != null) {
            updateGroup(groupCreateInfo);
        } else {
            insertGroup(groupCreateInfo);
        }
    }

    /**
     * 获取通信录列表sql
     *
     * @return
     */
    public String getUserListTableSql() {
        return manager.getEnterplorerHelper().getUserListTableSql();
    }

    /**
     * test rongCloud
     */
    public void openRongCloudDatabase() {
        if (manager.getEnterplorerHelper().getRongCloudDatabase(context) != null) {
            Log.e("RongCloud database", "成功");
        } else {
            Log.e("openRongCloudDatabase", "失败");
        }
    }

    public int getDbVersion() {
        int dbVersion = manager.getWritableDatabase().getVersion();
        return dbVersion;
    }

    /**
     * TEST SELECT Informations about message
     */
    public List<Message> getImMsg(String targetId, String searchKey) {
        SQLiteDatabase db = manager.getEnterplorerHelper().getRongCloudDatabase(context);
        List<Message> list = new ArrayList<>();
        NativeObject.Message message = new NativeObject.Message();
        Message msg = null;
        Cursor cursor = null;
        if (db != null && !TextUtils.isEmpty(searchKey)) {
            String sql = "select * from RCT_MESSAGE where target_id like '" + targetId + "'";
            try {
                cursor = db.rawQuery(sql, null);
                cursor.moveToFirst();
                while (cursor.moveToNext()) {
                    String a = cursor.getString(cursor.getColumnIndex("content"));
                    message.setConversationType(cursor.getInt(cursor.getColumnIndex("category_id")));
                    message.setTargetId(cursor.getString(cursor.getColumnIndex("target_id")));
                    message.setMessageId(cursor.getInt(cursor.getColumnIndex("id")));
                    message.setMessageDirection(cursor.getInt(cursor.getColumnIndex("message_direction")) == 0 ? false : true);
                    message.setSenderUserId(cursor.getString(cursor.getColumnIndex("sender_id")));
                    message.setReadStatus(cursor.getInt(cursor.getColumnIndex("read_status")));
                    message.setSentStatus(cursor.getInt(cursor.getColumnIndex("send_status")));
                    message.setReceivedTime(cursor.getLong(cursor.getColumnIndex("receive_time")));
                    message.setSentTime(cursor.getLong(cursor.getColumnIndex("send_time")));
                    message.setObjectName(cursor.getString(cursor.getColumnIndex("clazz_name")));
                    message.setContent(a.getBytes());
                    message.setExtra(cursor.getString(cursor.getColumnIndex("extra_content")));
                    msg = new Message(message);
                    if ("RC:TxtMsg".equals(message.getObjectName())) {
                        TextMessage textMessage = new TextMessage(a.getBytes());
                        msg.setContent(textMessage);
                        list.add(msg);
                    }
                }
            } catch (SQLException e) {
                Log.e("err", e.getMessage());
            } finally {
//                cursor.close();
//                db.close();
            }
        }
        return list;
    }
}
